

*****CLEAN FORCERANK DATA****
import delimited "$jfqa_rep/Forcerank.csv", clear

qui{
*drop repeated submissios
sort user_id contest_id record_id
drop if user_id==user_id[_n+1] & contest_id==contest_id[_n+1] 

foreach i in start end submit {
gen month=substr(`i',1, 2)
gen day=substr(`i',4, 2)
gen year=substr(`i',7, 2)

destring month, force replace
destring day, force replace
destring year, force replace
replace year=year+2000

gen date=mdy(month, day, year)

format date %tdnn/dd/YY

drop `i' year month day
rename date `i'
}

*drop students
foreach i in 3 81 84 85 {
drop if game_id==`i'
}

*drop ETFs and forex
foreach i in 38 82 83 12 37 {
drop if game_id==`i'
}

*drop games after 2017 and before March 2016
*keep if end<21184
drop if start<20514

*reshape data
reshape long rank_, i(record_id) j(rank)

rename rank_ ticker

replace rank=-rank+11

rename rank points

drop if end-start>7

gen year=year(start)
gen month=month(start)
gen day=day(start)
}

save "$jfqa_rep/Forcerank.dta", replace

keep ticker

duplicates drop

save "$jfqa_rep/Forcerank_Ticker.dta", replace




****FORCERANK SETUP*****
use PERMNO date RET PRC VOL DLRET DLSTCD TICKER CFACPR SHROUT using "${data_crsp}crsp_daily_all.dta", clear

qui{
rename PERMNO permno
rename RET ret
rename DLSTCD dlstcd
rename DLRET dlret
rename PRC prc
rename TICKER ticker
rename CFACPR cfacpr
rename SHROUT shrout

qui{
*trim sample to 2015-2018
gen year=year(date)
drop if year<2015
drop if year>2018

gen day=day(date)
gen month=month(date)

*delete duplicates and clean prc
drop if date==date[_n-1] & permno==permno[_n-1]
replace prc=abs(prc)
}

gen mktcap=prc*shrout
gen lag_mktcap=mktcap[_n-1] if permno==permno[_n-1]

*define adjusted prices for splits
gen prc_split=prc/cfacpr
drop cfacpr

*de-listed returns
replace ret=dlret if missing(ret)
replace ret=-0.3 if missing(ret) & dlstcd==500
replace ret=-0.3 if missing(ret) & dlstcd==520
replace ret=-0.3 if missing(ret) & dlstcd>550 & dlstcd<575
replace ret=-0.3 if missing(ret) & dlstcd==580
replace ret=-0.3 if missing(ret) & dlstcd==584
replace ret=-1 if missing(ret) & ~missing(dlstcd)
drop dlstcd dlret

drop if missing(prc_split) & missing(ret)

merge m:1 ticker using "$jfqa_rep/Forcerank_Ticker.dta"
keep if _merge==3
drop _merge

sort permno ticker date

gen fut1_ret=(1+ret)*(1+ret[_n+1])*(1+ret[_n+2])*(1+ret[_n+3])*(1+ret[_n+4])-1 if permno==permno[_n+4] 

gen lag1_ret=(1+ret[_n-1])*(1+ret[_n-2])*(1+ret[_n-3])*(1+ret[_n-4])*(1+ret[_n-5])-1 if permno==permno[_n-5] 
gen lag2_ret=lag1_ret[_n-5] if permno==permno[_n-5]
gen lag3_ret=lag1_ret[_n-10] if permno==permno[_n-10]
gen lag4_ret=lag1_ret[_n-15] if permno==permno[_n-15]
gen lag5_ret=lag1_ret[_n-20] if permno==permno[_n-20]
gen lag6_ret=lag1_ret[_n-25] if permno==permno[_n-25]
gen lag7_ret=lag1_ret[_n-30] if permno==permno[_n-30]
gen lag8_ret=lag1_ret[_n-35] if permno==permno[_n-35]
gen lag9_ret=lag1_ret[_n-40] if permno==permno[_n-40]
gen lag10_ret=lag1_ret[_n-45] if permno==permno[_n-45]
gen lag11_ret=lag1_ret[_n-50] if permno==permno[_n-50]
gen lag12_ret=lag1_ret[_n-55] if permno==permno[_n-55]

*generate convexity
gen price_0=prc_split

qui{
forval i=1/20 {
gen price_`i'=prc_split[_n-`i'] if permno==permno[_n-`i']
}
}

egen total_price=rowtotal(price_*)
gen avgprc=total_price/21

gen convexity_4=((price_0+price_20)/2-avgprc)/((price_0+price_20)/2)
gen lag_convexity=convexity_4[_n-1] if permno==permno[_n-1]
replace convexity_4=lag_convexity

drop price* total_price avgprc lag_convexity

gen price_0=prc_split

qui{
forval i=1/40 {
gen price_`i'=prc_split[_n-`i'] if permno==permno[_n-`i']
}
}

egen total_price=rowtotal(price_*)
gen avgprc=total_price/41

gen convexity_8=((price_0+price_40)/2-avgprc)/((price_0+price_40)/2)
gen lag_convexity=convexity_8[_n-1] if permno==permno[_n-1]
replace convexity_8=lag_convexity

drop price* total_price avgprc lag_convexity

gen price_0=prc_split

qui{
forval i=1/60 {
gen price_`i'=prc_split[_n-`i'] if permno==permno[_n-`i']
}
}

egen total_price=rowtotal(price_*)
gen avgprc=total_price/61

gen convexity_12=((price_0+price_60)/2-avgprc)/((price_0+price_60)/2)
gen lag_convexity=convexity_12[_n-1] if permno==permno[_n-1]
replace convexity_12=lag_convexity

drop price* total_price avgprc lag_convexity
}

collapse fut1_ret lag* convexity*, by(ticker year month day)

save "$jfqa_rep/22_Forcerank_setup.dta", replace
